import pandas as pd

# 定义列名和年份范围
years_0 = [str(y) for y in range(1990, 2001)]
id_vars = ['GeoFips', 'GeoName']
value_vars_0 = years_0

# 定义列名和年份范围
years_1 = [str(y) for y in range(2001, 2019)]
id_vars = ['GeoFips', 'GeoName']
value_vars_1 = years_1

# 加载并处理政府文职就业数据（Metro和Micro）
def load_and_process_civ_gov(file_path, msa_type, value_vars):
    df = pd.read_csv(file_path, skiprows=3)
    df = df.melt(id_vars=id_vars, value_vars=value_vars,
                 var_name='Year', value_name='CivilianGovtEmployment')
    df['Year'] = df['Year'].astype(int)
    df['MSAType'] = msa_type
    return df

civ_metro_0 = load_and_process_civ_gov("./data/civilian government employment 1990-2000 Metro.csv", 'Metro', value_vars_0)
civ_micro_0 = load_and_process_civ_gov('./data/civilian government employment 1990-2000 Micro.csv', 'Micro', value_vars_0)
civ_all_0 = pd.concat([civ_metro_0, civ_micro_0])
print(civ_all_0.shape)

civ_metro_1 = load_and_process_civ_gov("./data/civilian government employment 2001-current Metro.csv", 'Metro', value_vars_1)
civ_micro_1 = load_and_process_civ_gov("./data/civilian government employment 2001-current Micro.csv", 'Micro', value_vars_1)
civ_all_1 = pd.concat([civ_metro_1, civ_micro_1])
print(civ_all_1.shape)

civ_all = pd.concat([civ_all_0, civ_all_1])
print(civ_all.shape)


# 假设军事和总就业数据文件结构类似，此处需替换为实际文件名
# 示例：加载军事就业数据（假设文件名为类似格式）
def load_and_process_military(file_path, msa_type, value_vars):
    df = pd.read_csv(file_path, skiprows=3)
    df = df.melt(id_vars=id_vars, value_vars=value_vars,
                 var_name='Year', value_name='MilitaryEmployment')
    df['Year'] = df['Year'].astype(int)
    df['MSAType'] = msa_type
    return df

mil_metro_0 = load_and_process_military('./data/military employment 1990-2000 Metro.csv', 'Metro', value_vars_0)
mil_micro_0 = load_and_process_military('./data/military employment 1990-2000 Micro.csv', 'Micro', value_vars_0)
mil_all_0 = pd.concat([mil_metro_0, mil_micro_0])
print(mil_all_0.shape)

mil_metro_1 = load_and_process_military('./data/military employment 2001-current Metro.csv', 'Metro', value_vars_1)
mil_micro_1 = load_and_process_military('./data/military employment 2001-current Micro.csv', 'Micro', value_vars_1)
mil_all_1 = pd.concat([mil_metro_1, mil_micro_1])
print(mil_all_1.shape)

mil_all = pd.concat([mil_all_0, mil_all_1])
print(mil_all.shape)

# 合并所有数据（假设所有数据键为GeoFips, GeoName, Year）
merged = pd.merge(civ_all, mil_all, on=['GeoFips', 'GeoName', 'Year', 'MSAType'], how='outer')

# 检查不匹配的政府数据（29行）
unmatched = merged[merged[['MilitaryEmployment', 'TotalEmployment']].isnull().all(axis=1)]
print("不匹配的政府数据记录：", len(unmatched))
print("不匹配的MSA名称示例：", unmatched['GeoName'].unique())

# 删除不匹配记录
merged_clean = merged.dropna(subset=['MilitaryEmployment', 'TotalEmployment'], how='all')

# 验证最终数据形状
print("最终数据形状：", merged_clean.shape)
print("预期形状：(26854, 6) 实际形状：", merged_clean.shape)

# 输出最终数据（可选）
merged_clean.to_csv('final_merged_employment_data.csv', index=False)